#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute

"insert into  hive.edu_dm.datetime
    select distinct
           create_date_time,
           SUBSTRING(create_date_time, 1, 7) AS month_time,
           SUBSTRING(create_date_time, 1, 4) AS year_time
           from hive.edu_dws.hcx_dws_cus_day_cnt;

-- select
--     dt.month_time ,
--        sc.group_type,
--     sum(cus_cnt) as cus_amt
-- from hive.edu_dws.hcx_dws_cus_day_cnt sc
-- left join hive.edu_dm.datetime dt on sc.create_date_time = dt.create_date_time
-- group by dt.month_time,sc.group_type
-- having group_type='all'
--
--
-- select
--     dt.year_time ,
--        sc.group_type,
--     sum(cus_cnt) as cus_amt
-- from hive.edu_dws.hcx_dws_cus_day_cnt sc
-- left join hive.edu_dm.datetime dt on sc.create_date_time = dt.create_date_time
-- group by dt.year_time,sc.group_type
-- having group_type='all'
--
-- select
--     dt.month_time ,
--        sc.group_type,
--        sc.cus_area,
--     sum(cus_cnt) as cus_amt
-- from hive.edu_dws.hcx_dws_cus_day_cnt sc
-- left join hive.edu_dm.datetime dt on sc.create_date_time = dt.create_date_time
-- group by dt.month_time,sc.group_type,sc.cus_area
-- having group_type='area';
--
--
--
-- with temp1 as(select
--     dt.month_time ,
--     sc.cus_area,
--     sc.sub_name,
--     sc.sch_name,
--     sc.origin_channel,
--     sc.department_name,
--     sc.group_type as group_type_old,
--     case
--         when grouping(cus_area) = 0 then 'area'
--         when grouping(sub_name) = 0 then 'subject'
--         when grouping(sch_name) = 0 then 'school'
--         when grouping(origin_channel) = 0 then 'fromwhere'
--         when grouping(department_name) = 0 then 'chatcenter'
--         when grouping(group_type) = 0 then 'all' end
--         as group_type_new,
--     sum(cus_cnt) as cus_amt
-- from hive.edu_dws.hcx_dws_cus_day_cnt sc
-- left join hive.edu_dm.datetime dt on sc.create_date_time = dt.create_date_time
-- group by
-- grouping sets (
--                   (dt.month_time,sc.group_type),
--                   (dt.month_time,sc.group_type,sc.cus_area),
--                   (dt.month_time,sc.group_type,sc.sub_name),
--                   (dt.month_time,sc.group_type,sc.sch_name),
--                   (dt.month_time,sc.group_type,sc.origin_channel),
--                   (dt.month_time,sc.group_type,sc.department_name)
--     ))
--         select month_time,
--                cus_area,
--                sub_name,
--                sch_name,
--                origin_channel,
--                department_name,
-- --                group_type_old,
--                group_type_new as group_type,
--                cus_amt
-- from temp1
-- where group_type_new=group_type_old;
--
-- with temp2 as(select
--     dt.year_time ,
--     sc.cus_area,
--     sc.sub_name,
--     sc.sch_name,
--     sc.origin_channel,
--     sc.department_name,
--     sc.group_type as group_type_old,
--     case
--         when grouping(cus_area) = 0 then 'area'
--         when grouping(sub_name) = 0 then 'subject'
--         when grouping(sch_name) = 0 then 'school'
--         when grouping(origin_channel) = 0 then 'fromwhere'
--         when grouping(department_name) = 0 then 'chatcenter'
--         when grouping(group_type) = 0 then 'all' end
--         as group_type_new,
--     sum(cus_cnt) as cus_amt
-- from hive.edu_dws.hcx_dws_cus_day_cnt sc
-- left join hive.edu_dm.datetime dt on sc.create_date_time = dt.create_date_time
-- group by
-- grouping sets (
--                   (dt.year_time,sc.group_type),
--                   (dt.year_time,sc.group_type,sc.cus_area),
--                   (dt.year_time,sc.group_type,sc.sub_name),
--                   (dt.year_time,sc.group_type,sc.sch_name),
--                   (dt.year_time,sc.group_type,sc.origin_channel),
--                   (dt.year_time,sc.group_type,sc.department_name)
--     ))
--         select year_time,
--                cus_area,
--                sub_name,
--                sch_name,
--                origin_channel,
--                department_name,
-- --                group_type_old,
--                group_type_new as group_type,
--                cus_amt
-- from temp2
-- where group_type_new=group_type_old;
--
-- with temp3 as(select
--     dt.create_date_time ,
--     sc.cus_area,
--     sc.sub_name,
--     sc.sch_name,
--     sc.origin_ch2annel,
--     sc.department_name,
--     sc.group_type as group_type_old,
--     case
--         when grouping(cus_area) = 0 then 'area'
--         when grouping(sub_name) = 0 then 'subject'
--         when grouping(sch_name) = 0 then 'school'
--         when grouping(origin_channel) = 0 then 'fromwhere'
--         when grouping(department_name) = 0 then 'chatcenter'
--         when grouping(group_type) = 0 then 'all' end
--         as group_type_new,
--     sum(cus_cnt) as cus_amt
-- from hive.edu_dws.hcx_dws_cus_day_cnt sc
-- left join hive.edu_dm.datetime dt on sc.create_date_time = dt.create_date_time
-- group by
-- grouping sets (
--                   (dt.create_date_time,sc.group_type),
--                   (dt.create_date_time,sc.group_type,sc.cus_area),
--                   (dt.create_date_time,sc.group_type,sc.sub_name),
--                   (dt.create_date_time,sc.group_type,sc.sch_name),
--                   (dt.create_date_time,sc.group_type,sc.origin_channel),
--                   (dt.create_date_time,sc.group_type,sc.department_name)
--     ))
--         select create_date_time,
--                cus_area,
--                sub_name,
--                sch_name,
--                origin_channel,
--                department_name,
-- --                group_type_old,
--                group_type_new as group_type,
--                cus_amt
-- from temp3
-- where group_type_new=group_type_old;


insert into  hive.edu_dm.hcx_dm_cus_cnt
with temp as(select
    case
        when grouping(dt.create_date_time) = 0 then 'day'
        when grouping(month_time) = 0 then 'month'
        when grouping(year_time) = 0 then 'year' end as time_type,
    dt.create_date_time ,
    dt.month_time,
    dt.year_time,
     origin_type, clue_state,
    sc.cus_area,
    sc.sub_name,
    sc.sch_name,
    sc.origin_channel,
    sc.department_name,
    sc.group_type as group_type_old,
    case
        when grouping(cus_area) = 0 then 'area'
        when grouping(sub_name) = 0 then 'subject'
        when grouping(sch_name) = 0 then 'school'
        when grouping(origin_channel) = 0 then 'fromwhere'
        when grouping(department_name) = 0 then 'chatcenter'
        else 'all' end
        as group_type_new,
    sum(cus_cnt) as cus_amt
from hive.edu_dws.hcx_dws_cus_day_cnt sc
left join hive.edu_dm.datetime dt on sc.create_date_time = dt.create_date_time
group by
grouping sets (
                  (dt.year_time,origin_type, clue_state,sc.group_type),
                  (dt.year_time,origin_type, clue_state,sc.group_type,sc.cus_area),
                  (dt.year_time,origin_type, clue_state,sc.group_type,sc.sub_name),
                  (dt.year_time,origin_type, clue_state,sc.group_type,sc.sch_name),
                  (dt.year_time,origin_type, clue_state,sc.group_type,sc.origin_channel),
                  (dt.year_time,origin_type, clue_state,sc.group_type,sc.department_name),

                  (dt.year_time,dt.month_time,origin_type, clue_state,sc.group_type),
                  (dt.year_time,dt.month_time,origin_type, clue_state,sc.group_type,sc.cus_area),
                  (dt.year_time,dt.month_time,origin_type, clue_state,sc.group_type,sc.sub_name),
                  (dt.year_time,dt.month_time,origin_type, clue_state,sc.group_type,sc.sch_name),
                  (dt.year_time,dt.month_time,origin_type, clue_state,sc.group_type,sc.origin_channel),
                  (dt.year_time,dt.month_time,origin_type, clue_state,sc.group_type,sc.department_name)  ,


                  (dt.year_time,dt.month_time,dt.create_date_time,origin_type, clue_state,sc.group_type),
                  (dt.year_time,dt.month_time,dt.create_date_time,origin_type, clue_state,sc.group_type,sc.cus_area),
                  (dt.year_time,dt.month_time,dt.create_date_time,origin_type, clue_state,sc.group_type,sc.sub_name),
                  (dt.year_time,dt.month_time,dt.create_date_time,origin_type, clue_state,sc.group_type,sc.sch_name),
                  (dt.year_time,dt.month_time,dt.create_date_time,origin_type, clue_state,sc.group_type,sc.origin_channel),
                  (dt.year_time,dt.month_time,dt.create_date_time,origin_type, clue_state,sc.group_type,sc.department_name)
    ))
        select
               time_type,
               create_date_time,
               month_time,
               year_time,
               cus_area,
               sub_name,
               sch_name,
               origin_channel,
               department_name,

--                group_type_old,
               group_type_new as group_type,
               cus_amt
from temp
where group_type_new=group_type_old;"







